
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/01/2011 10:54:05
-- Generated from EDMX file: D:\Documents\Visual Studio 2010\Projects\sharecourse\DAL\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [sharecourse];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_TeacherCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_TeacherCourse];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseChat]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Chats] DROP CONSTRAINT [FK_CourseChat];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseHelp]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Helps] DROP CONSTRAINT [FK_CourseHelp];
GO
IF OBJECT_ID(N'[dbo].[FK_ChatChatReply]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ChatReplys] DROP CONSTRAINT [FK_ChatChatReply];
GO
IF OBJECT_ID(N'[dbo].[FK_HelpHelpReply]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HelpReplys] DROP CONSTRAINT [FK_HelpHelpReply];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseCoursePlcace]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CoursePlcaces] DROP CONSTRAINT [FK_CourseCoursePlcace];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseCourseInfo]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_CourseCourseInfo];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Teachers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Teachers];
GO
IF OBJECT_ID(N'[dbo].[AllThings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AllThings];
GO
IF OBJECT_ID(N'[dbo].[Courses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Courses];
GO
IF OBJECT_ID(N'[dbo].[Chats]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Chats];
GO
IF OBJECT_ID(N'[dbo].[Helps]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Helps];
GO
IF OBJECT_ID(N'[dbo].[ChatReplys]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ChatReplys];
GO
IF OBJECT_ID(N'[dbo].[HelpReplys]', 'U') IS NOT NULL
    DROP TABLE [dbo].[HelpReplys];
GO
IF OBJECT_ID(N'[dbo].[CoursePlcaces]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CoursePlcaces];
GO
IF OBJECT_ID(N'[dbo].[CourseInfos]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CourseInfos];
GO
IF OBJECT_ID(N'[dbo].[UserThings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserThings];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Teachers'
CREATE TABLE [dbo].[Teachers] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [TeacherId] nvarchar(max)  NOT NULL,
    [TeacherName] nvarchar(max)  NOT NULL,
    [Gender] nvarchar(max)  NOT NULL,
    [Department] nvarchar(max)  NOT NULL,
    [Position] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'AllThings'
CREATE TABLE [dbo].[AllThings] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Type] int  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [UserId] nvarchar(max)  NOT NULL,
    [UserPic] nvarchar(max)  NOT NULL,
    [ClassId] nvarchar(max)  NOT NULL,
    [ClassName] nvarchar(max)  NOT NULL,
    [Post] nvarchar(max)  NOT NULL,
    [Reply] nvarchar(max)  NOT NULL,
    [Time] datetime  NOT NULL,
    [Place] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Courses'
CREATE TABLE [dbo].[Courses] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [CourseName] nvarchar(max)  NOT NULL,
    [Time] nvarchar(max)  NOT NULL,
    [CapacityForUnder] nvarchar(max)  NOT NULL,
    [CapacityForGra] nvarchar(max)  NOT NULL,
    [Credit] nvarchar(max)  NOT NULL,
    [CourseId] nvarchar(max)  NOT NULL,
    [CourseSn] nvarchar(max)  NOT NULL,
    [Department] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Introduction] nvarchar(max)  NOT NULL,
    [TeacherID] int  NOT NULL,
    [IsExisting] bit  NOT NULL,
    [CourseInfo_ID] int  NOT NULL
);
GO

-- Creating table 'Chats'
CREATE TABLE [dbo].[Chats] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [UserId] nvarchar(max)  NOT NULL,
    [UserPic] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [Time] datetime  NOT NULL,
    [CourseID] int  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [UpdateTime] datetime  NOT NULL
);
GO

-- Creating table 'Helps'
CREATE TABLE [dbo].[Helps] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [UserId] nvarchar(max)  NOT NULL,
    [UserPic] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [Time] datetime  NOT NULL,
    [CourseID] int  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [UpdateTime] datetime  NOT NULL
);
GO

-- Creating table 'ChatReplys'
CREATE TABLE [dbo].[ChatReplys] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [UserId] nvarchar(max)  NOT NULL,
    [UserPic] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [Time] datetime  NOT NULL,
    [ChatID] int  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'HelpReplys'
CREATE TABLE [dbo].[HelpReplys] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [UserId] nvarchar(max)  NOT NULL,
    [UserPic] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [Time] datetime  NOT NULL,
    [HelpID] int  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'CoursePlcaces'
CREATE TABLE [dbo].[CoursePlcaces] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [CourseID] int  NOT NULL,
    [EditTime] datetime  NOT NULL,
    [EditorId] nvarchar(max)  NOT NULL,
    [Place] nvarchar(max)  NOT NULL,
    [EditorName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'CourseInfos'
CREATE TABLE [dbo].[CourseInfos] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Gold] float  NOT NULL,
    [Relax] float  NOT NULL,
    [Mark] float  NOT NULL,
    [RcmdNum] int  NOT NULL,
    [ScoredNum] int  NOT NULL,
    [Visits] int  NOT NULL
);
GO

-- Creating table 'UserThings'
CREATE TABLE [dbo].[UserThings] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [CourseId] nvarchar(max)  NOT NULL,
    [Gold] int  NOT NULL,
    [Relax] int  NOT NULL,
    [Mark] int  NOT NULL,
    [HasRcmd] bit  NOT NULL,
    [HasScored] bit  NOT NULL,
    [Visits] int  NOT NULL,
    [UserId] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Teachers'
ALTER TABLE [dbo].[Teachers]
ADD CONSTRAINT [PK_Teachers]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'AllThings'
ALTER TABLE [dbo].[AllThings]
ADD CONSTRAINT [PK_AllThings]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [PK_Courses]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Chats'
ALTER TABLE [dbo].[Chats]
ADD CONSTRAINT [PK_Chats]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Helps'
ALTER TABLE [dbo].[Helps]
ADD CONSTRAINT [PK_Helps]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ChatReplys'
ALTER TABLE [dbo].[ChatReplys]
ADD CONSTRAINT [PK_ChatReplys]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'HelpReplys'
ALTER TABLE [dbo].[HelpReplys]
ADD CONSTRAINT [PK_HelpReplys]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'CoursePlcaces'
ALTER TABLE [dbo].[CoursePlcaces]
ADD CONSTRAINT [PK_CoursePlcaces]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'CourseInfos'
ALTER TABLE [dbo].[CourseInfos]
ADD CONSTRAINT [PK_CourseInfos]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'UserThings'
ALTER TABLE [dbo].[UserThings]
ADD CONSTRAINT [PK_UserThings]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [TeacherID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_TeacherCourse]
    FOREIGN KEY ([TeacherID])
    REFERENCES [dbo].[Teachers]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeacherCourse'
CREATE INDEX [IX_FK_TeacherCourse]
ON [dbo].[Courses]
    ([TeacherID]);
GO

-- Creating foreign key on [CourseID] in table 'Chats'
ALTER TABLE [dbo].[Chats]
ADD CONSTRAINT [FK_CourseChat]
    FOREIGN KEY ([CourseID])
    REFERENCES [dbo].[Courses]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseChat'
CREATE INDEX [IX_FK_CourseChat]
ON [dbo].[Chats]
    ([CourseID]);
GO

-- Creating foreign key on [CourseID] in table 'Helps'
ALTER TABLE [dbo].[Helps]
ADD CONSTRAINT [FK_CourseHelp]
    FOREIGN KEY ([CourseID])
    REFERENCES [dbo].[Courses]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseHelp'
CREATE INDEX [IX_FK_CourseHelp]
ON [dbo].[Helps]
    ([CourseID]);
GO

-- Creating foreign key on [ChatID] in table 'ChatReplys'
ALTER TABLE [dbo].[ChatReplys]
ADD CONSTRAINT [FK_ChatChatReply]
    FOREIGN KEY ([ChatID])
    REFERENCES [dbo].[Chats]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ChatChatReply'
CREATE INDEX [IX_FK_ChatChatReply]
ON [dbo].[ChatReplys]
    ([ChatID]);
GO

-- Creating foreign key on [HelpID] in table 'HelpReplys'
ALTER TABLE [dbo].[HelpReplys]
ADD CONSTRAINT [FK_HelpHelpReply]
    FOREIGN KEY ([HelpID])
    REFERENCES [dbo].[Helps]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HelpHelpReply'
CREATE INDEX [IX_FK_HelpHelpReply]
ON [dbo].[HelpReplys]
    ([HelpID]);
GO

-- Creating foreign key on [CourseID] in table 'CoursePlcaces'
ALTER TABLE [dbo].[CoursePlcaces]
ADD CONSTRAINT [FK_CourseCoursePlcace]
    FOREIGN KEY ([CourseID])
    REFERENCES [dbo].[Courses]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseCoursePlcace'
CREATE INDEX [IX_FK_CourseCoursePlcace]
ON [dbo].[CoursePlcaces]
    ([CourseID]);
GO

-- Creating foreign key on [CourseInfo_ID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseCourseInfo]
    FOREIGN KEY ([CourseInfo_ID])
    REFERENCES [dbo].[CourseInfos]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseCourseInfo'
CREATE INDEX [IX_FK_CourseCourseInfo]
ON [dbo].[Courses]
    ([CourseInfo_ID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------